﻿using System;
using System.Data;
using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client;
using System.Web;
using System.Web.Caching;
using System.Collections.Generic;

using LMD.Constant;
using LMD.Lib.DataClass;
using LMD.Lib.Oracle;

namespace Cached
{
    /// <summary>
    /// Summary description for KonfigurasiDatekTable
    /// </summary>
    public class KonfigurasiDatekTable : CacheAbstraction
    {
		String sxFilterHierarki_Sales =
            @"
            and v1.SALES_EMPLOYEE_NO IN
            (
                select :prmID from dual
                UNION ALL
                select EMPLOYEE_NO from slogix.p_slx_usergroup_member where p_slx_usergroup_id IN
                (
                    select 
                        p_slx_usergroup_id 
                    from 
                        slogix.p_slx_usergroup
                    start with 
                        p_slx_usergroup_id =
                        (
                            select max(a)
                            from 
                            (
                                select 
                                    decode(ishead, 'Y', p_slx_usergroup_id, -1) a
                                from 
                                    slogix.p_slx_usergroup_member 
                                where EMPLOYEE_NO = :prmID
                            )
                        ) 
                    connect by prior 
                        p_slx_usergroup_id = parent_id
                )
            )";

        String sxFilterHierarki_CAR =
            @"
            and v1.CAR_EMPLOYEE_NO IN
            (  
                select :prmID from dual
                UNION ALL          
                select 
                    NIK 
                from 
                    v_icp_escalation
                /**/start with 
                    NIK = :prmID
                connect by prior 
                    t_qrtz_user_destination_id = parent_id
				-- EMERGENCY RELEASE FROM RDY
				-- ORACLE 11G DOESN'T SUPPORT CONNECT BY
				/*where nik = :prmID --'74940445'
                      and t_qrtz_user_destination_id in(
                            select v.t_qrtz_user_destination_id
                            from   v_icp_escalation v,
                                   sni.t_qrtz_user_destination w
                            where  (w.t_qrtz_user_destination_id = v.parent_id) 
							)*/
            )";

        public DataTable LoadCDList()
        {
            return LoadCDList(false);
        }
        public DataTable LoadCDList(bool bForceRefresh)
        {
            String sxCacheKey = KCache.T_KONFIGURASI_DATEK+"_"+ICPUser.ID;

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }
            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();
                DataTable dt;
                if (ICPUser.IsAdmin || ICPUser.IsAdminForum || ICPUser.IsHelpDesk || ICPUser.IsSSE)
                {
                    String sxQuery =
                    @"
                    select
                        t1.T_KONFIGURASI_DATEK_ID,
                        t1.NOTE,
                        t1.INFO,
                        t1.KONFIG_FILE_ID,
                        t1.DATEK_FILE_ID,
                        t1.EVALUASI_FILE_ID,
                        t2.ORIGINAL_FILENAME KONFIG_FILENAME,
                        t3.ORIGINAL_FILENAME DATEK_FILENAME,
                        t4.ORIGINAL_FILENAME EVALUASI_FILENAME,
                        t1.CUSTOMER_ID,
                        v1.COMPANY_NAME,
                        v1.CAR_EMPLOYEE_NO EMPLOYEE_NO,
                        v1.CAR_NAME CAR,
                        V1.P_CUSTOMER_CLASS_CODE COMPANY_CLASS,
                        --nvl(t1.UPDATE_DATE,t1.CREATE_DATE) UPLOAD_TIME,
                        t1.CREATE_DATE,
                        emp1.EMPLOYEE_NAME CREATE_BY_NAME,
                        t1.UPDATE_DATE,
                        emp2.EMPLOYEE_NAME UPDATE_BY_NAME
                    from
                        T_KONFIGURASI_DATEK t1
                    left join
                        T_FILE_UPLOAD t2
                    on
                        t1.KONFIG_FILE_ID = t2.T_FILE_UPLOAD_ID
                    left join
                        T_FILE_UPLOAD t3
                    on
                        t1.DATEK_FILE_ID = t3.T_FILE_UPLOAD_ID
                    left join
                        T_FILE_UPLOAD t4
                    on
                        t1.EVALUASI_FILE_ID = t4.T_FILE_UPLOAD_ID
                    join
                        V_ICP_CUSTOMER_COMPLETE v1
                    on
                        t1.CUSTOMER_ID = v1.CUSTOMER_ID
                    left join 
                        slogix.employee emp1
                    on
                        t1.CREATE_BY = emp1.EMPLOYEE_NO
                    left join 
                        slogix.employee emp2
                    on
                        t1.UPDATE_BY = emp2.EMPLOYEE_NO   
                    order by
                        CREATE_DATE desc";

                    dt = dbl.GetDataTable(sxQuery);
                }
                else
                {
                    List<OracleParameter> lsParams = new List<OracleParameter>();
                    lsParams.Add(new OracleParameter());
                    lsParams[lsParams.Count - 1].ParameterName = "prmID";
                    lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
                    lsParams[lsParams.Count - 1].Value = ICPUser.ID;
                    String sxQuery =
                    @"
                    select
                        t1.T_KONFIGURASI_DATEK_ID,
                        t1.NOTE,
                        t1.INFO,
                        t1.KONFIG_FILE_ID,
                        t1.DATEK_FILE_ID,
                        t1.EVALUASI_FILE_ID,
                        t2.ORIGINAL_FILENAME KONFIG_FILENAME,
                        t3.ORIGINAL_FILENAME DATEK_FILENAME,
                        t4.ORIGINAL_FILENAME EVALUASI_FILENAME,
                        t1.CUSTOMER_ID,
                        v1.COMPANY_NAME,
                        v1.CAR_EMPLOYEE_NO EMPLOYEE_NO,
                        v1.CAR_NAME CAR,
                        V1.P_CUSTOMER_CLASS_CODE COMPANY_CLASS,
                        --nvl(t1.UPDATE_DATE,t1.CREATE_DATE) UPLOAD_TIME,
                        t1.CREATE_DATE,
                        emp1.EMPLOYEE_NAME CREATE_BY_NAME,
                        t1.UPDATE_DATE,
                        emp2.EMPLOYEE_NAME UPDATE_BY_NAME
                    from
                        T_KONFIGURASI_DATEK t1
                    left join
                        T_FILE_UPLOAD t2
                    on
                        t1.KONFIG_FILE_ID = t2.T_FILE_UPLOAD_ID
                    left join
                        T_FILE_UPLOAD t3
                    on
                        t1.DATEK_FILE_ID = t3.T_FILE_UPLOAD_ID
                    left join
                        T_FILE_UPLOAD t4
                    on
                        t1.EVALUASI_FILE_ID = t4.T_FILE_UPLOAD_ID
                    join
                        V_ICP_CUSTOMER_COMPLETE v1
                    on
                        t1.CUSTOMER_ID = v1.CUSTOMER_ID
					left join 
                        slogix.employee emp1
                    on
                        t1.CREATE_BY = emp1.EMPLOYEE_NO
                    left join 
                        slogix.employee emp2
                    on
                        t1.UPDATE_BY = emp2.EMPLOYEE_NO
                    {0}
                    order by
                        CREATE_DATE desc";

                    if (ICPUser.RoleId == (int)ICPUserRole.CAR || ICPUser.RoleId == (int)ICPUserRole.CARAdminForum
						|| ICPUser.RoleId == (int)ICPUserRole.CARPimpinanCAR)
                    {
                        sxQuery = String.Format(sxQuery, sxFilterHierarki_CAR);
                    }
                    else if (ICPUser.RoleId == (int)ICPUserRole.Sales)
                    {
                        sxQuery = String.Format(sxQuery, sxFilterHierarki_Sales);
                    }
                    else
                    {
                        sxQuery = String.Format(sxQuery, String.Empty);
                    }
                    dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
                }
                AddToCache(sxCacheKey, dt, CacheType.FiveMinutes);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        //edited by Rista
        public DataTable LoadCDList(int iStartDate, int iEndDate, int iStartMonth, int iStartYear, int iEndMonth, int iEndYear)
        {
            return LoadCDList(iStartDate, iEndDate, iStartMonth, iStartYear, iEndMonth, iEndYear, false);
        }
        public DataTable LoadCDList(int iStartDate, int iEndDate, int iStartMonth, int iStartYear, int iEndMonth, int iEndYear, bool bForceRefresh)
        {
            //String sxCacheKey = KCache.T_KONFIGURASI_DATEK + "_" + ICPUser.ID;

            String sxCacheKey = String.Format("{0}_{1}_{2}{3}_{4}{5}_{6}{7}", KCache.T_KONFIGURASI_DATEK, ICPUser.ID,
                iStartDate.ToString(),
                iEndDate.ToString(),
                iStartMonth.ToString(),
                iStartYear.ToString(),
                iEndMonth.ToString(),
                iEndYear.ToString());

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }
            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();
                DataTable dt;
                string sxExtraParam = "";

                List<OracleParameter> lsParams = new List<OracleParameter>();
                //add param
                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartDate";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iStartDate;
                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndDate";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iEndDate;
                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iStartMonth;
                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iEndMonth;
                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iStartYear;
                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iEndYear;

                /*sxExtraParam +=
                @"where 
                (
                    t1.CREATE_DATE >= to_date(:prmStartDate || '\' || :prmStartMonth || '\' || :prmStartYear, 'dd\mm\yyyy')
                    and
                    t1.CREATE_DATE <= to_date(:prmEndDate || '\' || :prmEndMonth || '\' || :prmEndYear, 'dd\mm\yyyy')
                )";*/
                sxExtraParam = @"where trunc(t1.CREATE_DATE) between to_date(:prmStartDate || '\' || :prmStartMonth || '\' || :prmStartYear, 'dd\mm\yyyy')
                            and to_date(:prmEndDate || '\' || :prmEndMonth || '\' || :prmEndYear, 'dd\mm\yyyy')";

                if (iStartDate == 1 && iEndDate == 1 && iStartMonth == 1 && iEndMonth == 1 && iStartYear == 1 && iEndYear == 1)
                {
                    sxExtraParam = "";
                    for (int i = 5; i >= 0; i--)
                    {
                        lsParams.RemoveAt(i);
                    }
                }

                if (ICPUser.IsAdmin || ICPUser.IsAdminForum || ICPUser.IsHelpDesk || ICPUser.IsSSE)
                {
                    String sxQuery = String.Format(
                    @"
                    select
                        t1.T_KONFIGURASI_DATEK_ID,
                        t1.NOTE,
                        t1.INFO,
                        t1.KONFIG_FILE_ID,
                        t1.DATEK_FILE_ID,
                        t1.EVALUASI_FILE_ID,
                        t2.ORIGINAL_FILENAME KONFIG_FILENAME,
                        t3.ORIGINAL_FILENAME DATEK_FILENAME,
                        t4.ORIGINAL_FILENAME EVALUASI_FILENAME,
                        t1.CUSTOMER_ID,
                        v1.COMPANY_NAME,
                        v1.CAR_EMPLOYEE_NO EMPLOYEE_NO,
                        v1.CAR_NAME CAR,
                        V1.P_CUSTOMER_CLASS_CODE COMPANY_CLASS,
                        --nvl(t1.UPDATE_DATE,t1.CREATE_DATE) UPLOAD_TIME,
                        t1.CREATE_DATE,
                        emp1.EMPLOYEE_NAME CREATE_BY_NAME,
                        t1.UPDATE_DATE,
                        emp2.EMPLOYEE_NAME UPDATE_BY_NAME
                    from
                        T_KONFIGURASI_DATEK t1
                    left join
                        T_FILE_UPLOAD t2
                    on
                        t1.KONFIG_FILE_ID = t2.T_FILE_UPLOAD_ID
                    left join
                        T_FILE_UPLOAD t3
                    on
                        t1.DATEK_FILE_ID = t3.T_FILE_UPLOAD_ID
                    left join
                        T_FILE_UPLOAD t4
                    on
                        t1.EVALUASI_FILE_ID = t4.T_FILE_UPLOAD_ID
                    join
                        V_ICP_CUSTOMER_COMPLETE v1
                    on
                        t1.CUSTOMER_ID = v1.CUSTOMER_ID
                    left join 
                        slogix.employee emp1
                    on
                        t1.CREATE_BY = emp1.EMPLOYEE_NO
                    left join 
                        slogix.employee emp2
                    on
                        t1.UPDATE_BY = emp2.EMPLOYEE_NO
                    {0}   
                    order by
                        CREATE_DATE desc", sxExtraParam);

                    dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
                }
                else
                {
                    //List<OracleParameter> lsParams = new List<OracleParameter>();
                    /*
                     * Commented => unused variable
                     * lsParams.Add(new OracleParameter());
                    lsParams[lsParams.Count - 1].ParameterName = "prmID";
                    lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
                    lsParams[lsParams.Count - 1].Value = ICPUser.ID;*/
                    
                    String sxQuery = String.Format(
                    @"
                    select
                        t1.T_KONFIGURASI_DATEK_ID,
                        t1.NOTE,
                        t1.INFO,
                        t1.KONFIG_FILE_ID,
                        t1.DATEK_FILE_ID,
                        t1.EVALUASI_FILE_ID,
                        t2.ORIGINAL_FILENAME KONFIG_FILENAME,
                        t3.ORIGINAL_FILENAME DATEK_FILENAME,
                        t4.ORIGINAL_FILENAME EVALUASI_FILENAME,
                        t1.CUSTOMER_ID,
                        v1.COMPANY_NAME,
                        v1.CAR_EMPLOYEE_NO EMPLOYEE_NO,
                        v1.CAR_NAME CAR,
                        V1.P_CUSTOMER_CLASS_CODE COMPANY_CLASS,
                        --nvl(t1.UPDATE_DATE,t1.CREATE_DATE) UPLOAD_TIME,
                        t1.CREATE_DATE,
                        emp1.EMPLOYEE_NAME CREATE_BY_NAME,
                        t1.UPDATE_DATE,
                        emp2.EMPLOYEE_NAME UPDATE_BY_NAME
                    from
                        T_KONFIGURASI_DATEK t1
                    left join
                        T_FILE_UPLOAD t2
                    on
                        t1.KONFIG_FILE_ID = t2.T_FILE_UPLOAD_ID
                    left join
                        T_FILE_UPLOAD t3
                    on
                        t1.DATEK_FILE_ID = t3.T_FILE_UPLOAD_ID
                    left join
                        T_FILE_UPLOAD t4
                    on
                        t1.EVALUASI_FILE_ID = t4.T_FILE_UPLOAD_ID
                    join
                        V_ICP_CUSTOMER_COMPLETE v1
                    on
                        t1.CUSTOMER_ID = v1.CUSTOMER_ID
					left join 
                        slogix.employee emp1
                    on
                        t1.CREATE_BY = emp1.EMPLOYEE_NO
                    left join 
                        slogix.employee emp2
                    on
                        t1.UPDATE_BY = emp2.EMPLOYEE_NO
                    {0}
                    order by
                        CREATE_DATE desc", sxExtraParam);

                    if (ICPUser.RoleId == (int)ICPUserRole.CAR || ICPUser.RoleId == (int)ICPUserRole.CARAdminForum
                        || ICPUser.RoleId == (int)ICPUserRole.CARPimpinanCAR)
                    {
                        sxQuery = String.Format(sxQuery, sxFilterHierarki_CAR);
                    }
                    else if (ICPUser.RoleId == (int)ICPUserRole.Sales)
                    {
                        sxQuery = String.Format(sxQuery, sxFilterHierarki_Sales);
                    }
                    else
                    {
                        sxQuery = String.Format(sxQuery, String.Empty);
                    }
                    dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
                }
                AddToCache(sxCacheKey, dt, CacheType.FiveMinutes);

            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }
        //End of edited
        public CDData LoadCDDataByCustomerId(int iCustomerID, int iCDID)
        {
            return LoadCDDataByCustomerId(iCustomerID, iCDID, false);
        }
        public CDData LoadCDDataByCustomerId(int iCustomerID,int iCDID, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}{2}", 
                KCache.T_KONFIGURASI_DATEK, 
                iCustomerID.ToString(),
                iCDID.ToString());

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }
            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();
                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmCustomerID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCustomerID;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmCDID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCDID;

                String sxQuery =
                @"
                select
                    t1.T_KONFIGURASI_DATEK_ID,
                    t1.NOTE,
                    t1.INFO,
                    t1.KONFIG_FILE_ID,
                    t1.DATEK_FILE_ID,
                    t1.EVALUASI_FILE_ID,
                    t2.ORIGINAL_FILENAME KONFIG_FILENAME,
                    t3.ORIGINAL_FILENAME DATEK_FILENAME,
                    t4.ORIGINAL_FILENAME EVALUASI_FILENAME,
                    t1.CUSTOMER_ID,
                    v1.COMPANY_NAME
                from
                    T_KONFIGURASI_DATEK t1
                left join
                    T_FILE_UPLOAD t2
                on
                    t1.KONFIG_FILE_ID = t2.T_FILE_UPLOAD_ID
                left join
                    T_FILE_UPLOAD t3
                on
                    t1.DATEK_FILE_ID = t3.T_FILE_UPLOAD_ID
                left join
                    T_FILE_UPLOAD t4
                on
                    t1.EVALUASI_FILE_ID = t4.T_FILE_UPLOAD_ID
                left join
                    V_ICP_COMPANY_CAR_SALES v1
                on
                    t1.CUSTOMER_ID = v1.CUSTOMER_ID
                where
                    t1.CUSTOMER_ID = :prmCustomerID
                    and t1.T_KONFIGURASI_DATEK_ID = :prmCDID";

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
                CDData cdData;
                if (dt.Rows.Count > 0)
                {
                    cdData = new CDData();
                    cdData.iCDID = Convert.ToInt32(dt.Rows[0]["T_KONFIGURASI_DATEK_ID"]);
                    if (dt.Rows[0]["KONFIG_FILE_ID"] != DBNull.Value)
                    {
                        cdData.iConfigFileID = Convert.ToInt32(dt.Rows[0]["KONFIG_FILE_ID"]);
                    }
                    if (dt.Rows[0]["DATEK_FILE_ID"] != DBNull.Value)
                    {
                        cdData.iDatekFileID = Convert.ToInt32(dt.Rows[0]["DATEK_FILE_ID"]);
                    }
                    if (dt.Rows[0]["EVALUASI_FILE_ID"] != DBNull.Value)
                    {
                        cdData.iEvaluasiFileID = Convert.ToInt32(dt.Rows[0]["EVALUASI_FILE_ID"]);
                    }
                    if (dt.Rows[0]["NOTE"] != DBNull.Value)
                    {
                        cdData.sxNote = dt.Rows[0]["NOTE"].ToString();
                    }
                    if (dt.Rows[0]["INFO"] != DBNull.Value)
                    {
                        cdData.sxInfo = dt.Rows[0]["INFO"].ToString();
                    }
                    if (dt.Rows[0]["KONFIG_FILENAME"] != DBNull.Value)
                    {
                        cdData.sxConfigFileName = dt.Rows[0]["KONFIG_FILENAME"].ToString();
                    }
                    if (dt.Rows[0]["DATEK_FILENAME"] != DBNull.Value)
                    {
                        cdData.sxDatekFileName = dt.Rows[0]["DATEK_FILENAME"].ToString();
                    }
                    if (dt.Rows[0]["EVALUASI_FILENAME"] != DBNull.Value)
                    {
                        cdData.sxEvaluasiFileName = dt.Rows[0]["EVALUASI_FILENAME"].ToString();
                    }
                    if (dt.Rows[0]["COMPANY_NAME"] != DBNull.Value)
                    {
                        cdData.sxCompanyName = dt.Rows[0]["COMPANY_NAME"].ToString();
                    }
                    if (dt.Rows[0]["CUSTOMER_ID"] != DBNull.Value)
                    {
                        cdData.iCustomerID = Convert.ToInt32(dt.Rows[0]["CUSTOMER_ID"]);
                    }
                }
                else
                {
                    cdData = new CDData();
                }
                AddToCache(sxCacheKey, cdData, CacheType.FiveMinutes);
                
            }
            return (CDData)HttpRuntime.Cache[sxCacheKey];
        }
        public DataTable LoadCDReport()
        {
            return LoadCDReport(false);
        }
        public DataTable LoadCDReport(bool bForceRefresh)
        {
            String sxCacheKey = KCache.T_KONFIGURASI_DATEK + "_REPORT";

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }
            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();

                String sxQuery =
                @"
                select
                    COMPANY_CLASS,
                    JUMLAH_PELANGGAN,
                    JUMLAH_KONFIG,
                    PERSEN_KONFIG,
                    JUMLAH_DATEK,
                    PERSEN_DATEK,
					JUMLAH_EVAL,
					PERSEN_EVAL
                from
                    V_ICP_KONFIG_DATEK_BYCLASS";

                DataTable dt = dbl.GetDataTable(sxQuery);

                AddToCache(sxCacheKey, dt, CacheType.FiveMinutes);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }
        public DataTable LoadCDReportCAR()
        {
            return LoadCDReportCAR(false);
        }
        public DataTable LoadCDReportCAR(bool bForceRefresh)
        {
            String sxCacheKey = KCache.T_KONFIGURASI_DATEK + "_REPORT_CAR_"+ICPUser.ID;

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }
            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();
                DataTable dt;
                if (ICPUser.IsAdmin || ICPUser.IsAdminForum)
                {
                    String sxQuery =
                    @"
                    select
                        EMPLOYEE_NO,
                        EMPLOYEE_NAME,
                        JUMLAH_PELANGGAN,
                        JUMLAH_KONFIG,
                        PERSEN_KONFIG,
                        JUMLAH_DATEK,
                        PERSEN_DATEK,
                        JUMLAH_EVAL,
                        PERSEN_EVAL
                    from
                        V_ICP_KONFIGURASI_DATEK_BY_CAR
                    ";

                    dt = dbl.GetDataTable(sxQuery);
                }
                else
                {
                    List<OracleParameter> lsParams = new List<OracleParameter>();
                    lsParams.Add(new OracleParameter());
                    lsParams[lsParams.Count - 1].ParameterName = "prmCARId";
                    lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
                    lsParams[lsParams.Count - 1].Value = ICPUser.ID;

                    String sxQuery =
                    @"
                    select
                        EMPLOYEE_NO,
                        EMPLOYEE_NAME,
                        JUMLAH_PELANGGAN,
                        JUMLAH_KONFIG,
                        PERSEN_KONFIG,
                        JUMLAH_DATEK,
                        PERSEN_DATEK,
                        JUMLAH_EVAL,
                        PERSEN_EVAL
                    from
                        V_ICP_KONFIGURASI_DATEK_BY_CAR
                    where
                        EMPLOYEE_NO IN
                        (
                            select :prmCARId from dual
							UNION ALL
							SELECT DISTINCT NIK
                            FROM 
                            v_icp_escalation
                            START WITH NIK = :prmCARId        
                            CONNECT BY PRIOR t_qrtz_user_destination_id = parent_id
                        )";

                    dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
                }
                AddToCache(sxCacheKey, dt, CacheType.FiveMinutes);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }
        public DataTable LoadCDReportMode(String sxClass, String sxType)
        {
            return LoadCDReportMode(sxClass, sxType, false);
        }
        public DataTable LoadCDReportMode(String sxClass, String sxType, bool bForceRefresh)
        {
            String sxCacheKey = KCache.T_KONFIGURASI_DATEK + "_REPORT"+sxClass+sxType+"_"+ICPUser.ID;

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }
            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();
                DataTable dt;
                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmClass";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
                lsParams[lsParams.Count - 1].Value = sxClass;
                if (ICPUser.IsAdmin || ICPUser.IsAdminForum)
                {
                    String sxQuery = String.Format(
                    @"
                    select
                        vtmp1.CUSTOMER_ID,
                        vtmp1.COMPANY_NAME,
                        vtmp1.EMPLOYEE_NO,
                        vtmp1.EMPLOYEE_NAME CAR,
                        vtmp1.COMPANY_CLASS,
                        t1.NOTE,
                        t1.INFO,
                        t1.{0}_FILE_ID FILE_ID,
                        t2.ORIGINAL_FILENAME FILENAME   
                    from
                        t_konfigurasi_datek t1
                    join
                        t_file_upload t2
                    on
                        t1.{0}_FILE_ID = t2.T_FILE_UPLOAD_ID 
                    join
                        (   
                        select
                            v1.CUSTOMER_ID,
                            v1.COMPANY_NAME,
                            v2.EMPLOYEE_NO,
                            v2.EMPLOYEE_NAME,
                            v2.COMPANY_CLASS
                        from
                            V_ICP_CUSTOMER v1
                        left join
                            v_ICP_CAR v2 
                        on 
                            v1.CUSTOMER_ID = v2.COMPANY_ID
                        where
                            v2.COMPANY_CLASS = :prmClass
                        ) vtmp1
                    on
                        t1.CUSTOMER_ID = vtmp1.CUSTOMER_ID",
                    sxType.ToUpper());

                    dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
                }
                else
                {
                    lsParams.Add(new OracleParameter());
                    lsParams[lsParams.Count - 1].ParameterName = "prmCARId";
                    lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
                    lsParams[lsParams.Count - 1].Value = ICPUser.ID;

                    String sxQuery = String.Format(
                    @"
                    select
                        vtmp1.CUSTOMER_ID,
                        vtmp1.COMPANY_NAME,
                        vtmp1.EMPLOYEE_NO,
                        vtmp1.EMPLOYEE_NAME CAR,
                        vtmp1.COMPANY_CLASS,
                        t1.NOTE,
                        t1.INFO,
                        t1.{0}_FILE_ID FILE_ID,
                        t2.ORIGINAL_FILENAME FILENAME   
                    from
                        t_konfigurasi_datek t1
                    join
                        t_file_upload t2
                    on
                        t1.{0}_FILE_ID = t2.T_FILE_UPLOAD_ID 
                    join
                        (   
                        select
                            v1.CUSTOMER_ID,
                            v1.COMPANY_NAME,
                            v2.EMPLOYEE_NO,
                            v2.EMPLOYEE_NAME,
                            v2.COMPANY_CLASS
                        from
                            V_ICP_CUSTOMER v1
                        left join
                            v_ICP_CAR v2 
                        on 
                            v1.CUSTOMER_ID = v2.COMPANY_ID
                        where
                            v2.COMPANY_CLASS = :prmClass
                        and
                            v2.EMPLOYEE_NO IN
                            (
                                select :prmCARId from dual
								UNION ALL
								SELECT DISTINCT NIK
                                FROM 
                                v_icp_escalation
                                START WITH NIK = :prmCARId        
                                CONNECT BY PRIOR t_qrtz_user_destination_id = parent_id
                            )
                        ) vtmp1
                    on
                        t1.CUSTOMER_ID = vtmp1.CUSTOMER_ID",
                        sxType.ToUpper());

                    dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());
                }
                AddToCache(sxCacheKey, dt, CacheType.FiveMinutes);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }
        public DataTable LoadCustomerNetwork(int iCustomerId)
        {
            return LoadCustomerNetwork(iCustomerId, false);
        }
        public DataTable LoadCustomerNetwork(int iCustomerId, bool bForceRefresh)
        {
            String sxCacheKey = KCache.T_KONFIGURASI_DATEK+"NETWORK_"+iCustomerId.ToString();

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }
            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib();
                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmCustomerID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCustomerId;
                String sxQuery =
                @"
                select
                    NETWORK_NO,
                    CUSTOMER_ID
                from
                    V_ICP_CUSTOMER_NETWORK
                where
                    CUSTOMER_ID = :prmCustomerID
                ";

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.FiveMinutes);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }
        
        public override String GetCacheFilename()
        {
            return KCache.Filename.CONFIG_DATEK;
        }
    }
}